package Model;


import java.io.File;
import java.io.FileInputStream;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import javax.swing.JOptionPane;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Tan Le Anh
 */
public class Connect {
    String servername=null;
    String port=null;
    String database=null;
    String username=null;
    String password=null;
    
    public Connection dtConnect=null;
    
    public Connect(String _server,String _database,String _user,String _pass){
        servername=_server;
        port="1433";
        database=_database;
        username=_user;
        password=_pass;
        
        try
        {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            dtConnect=DriverManager.getConnection(getConnectionString());
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }
    
    public String getConnectionString() throws Exception
    {
        String kq="jdbc:sqlserver://";
        kq+=servername;
        kq+=":";
        kq+=port;
        kq+=";databaseName=";
        kq+=database;
        kq+=";user=";
        kq+=username;
        kq+=";password=";
        kq+=password;
        return kq;
    }
    
    public ResultSet execQuery(String sql)
    {
        ResultSet rs =null;
        try
        {
            java.sql.Statement query=dtConnect.createStatement();
            rs = query.executeQuery(sql);
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        return rs;
    }
    
    public void exec(String sql)
    {        
        try
        {
            java.sql.Statement query=dtConnect.createStatement();
            query.execute(sql);
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }        
    }
    DateFormat df= new SimpleDateFormat("yyyy-MM-dd");
    public void execPreparedStatement(String sql,String filename,String tenhs,String ngaysinh,String ghichu, String mahs)
    {
        FileInputStream fis = null;
        try {
            File f=new File(filename);
            fis = new FileInputStream(f);                        
            PreparedStatement ps=dtConnect.prepareStatement(sql);
            ps.setString(1, tenhs);
            ps.setDate(2,new Date(df.parse(ngaysinh).getTime()) );
            ps.setString(3, ghichu);
            ps.setBinaryStream(4, fis);
            if(!mahs.equals(""))
            {
                ps.setInt(5, Integer.parseInt(mahs));
            }
            ps.executeUpdate();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,"Ngày sinh không được trống!\n"+ ex.getMessage());
        } 
    }
    
    public void close() throws Exception{
        if(dtConnect!=null)
        {
            dtConnect.close();
        }
    }
}
